/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package com.informet.ipd.modelo.dao;

import com.informet.ipd.modelo.vo.ProgEstrMarLogVO;
import com.informet.ipd.modelo.vo.ProgEstrVO;
import com.informet.ipd.modelo.vo.RolUsuarioVO;
import com.informet.ipd.modelo.vo.UsuarioVO;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import javax.swing.JOptionPane;

/**
 *
 * @author LAPTOP01
 */
public class ProgEstrMarLogDao extends DBase{


  Connection conn = null;

    public ProgEstrMarLogDao() {
        super();
    }

    public void  setDetailMarLogFin(ProgEstrMarLogVO bean, ResultSet rs) throws SQLException{
        bean.setFIN_COD_PRG(rs.getInt("NUM_FINPRG_PK"));
        bean.setFIN_DESC_PRG(rs.getString("TX_DESCFINPRG"));
        bean.setFIN_FUENTE_PRG(rs.getString("TX_FUENTEFINPRG"));
        bean.setFIN_SUPUESTO_PRG(rs.getString("TX_SUPUESTOFINPRG"));
    }


    public void  setFinInv(ProgEstrMarLogVO bean, ResultSet rs) throws SQLException{
        bean.setFIN_COD_PRG(rs.getInt("NUM_FINPRY_PK"));
        bean.setFIN_DESC_PRG(rs.getString("TX_DESCFINPRY"));
        bean.setFIN_FUENTE_PRG(rs.getString("TX_FUENTEFINPRY"));
        bean.setFIN_SUPUESTO_PRG(rs.getString("TX_SUPUESTOFINPRY"));
        bean.setFIN_PRESUPPRG(rs.getBigDecimal("NUM_PRESUFINPPRY"));

    }
    
    
    
    public void  setMuestraF(ProgEstrMarLogVO bean, ResultSet rs) throws SQLException{
        bean.setFIN_COD_PRG(rs.getInt("NUM_FINPRY_PK"));
    }



     public void  setDetailMarLogProposito(ProgEstrMarLogVO bean, ResultSet rs) throws SQLException{
//        bean.setPROP_COD_PPRG(rs.getInt("NUM_PROPPRG_PK"));
//        bean.setPROP_DESC_PRG(rs.getString("TX_DESCPROPPRG"));
//        bean.setPROP_FUENTE_PRG(rs.getString("TX_FUENTEPROPPRG"));
//        bean.setPROP_SUPUESTO_PRG(rs.getString("TX_SUPUESTOPROPPRG"));
        bean.setFIN_COD_PRG(rs.getInt("NUM_PROPPRG_PK"));
        bean.setFIN_DESC_PRG(rs.getString("TX_DESCPROPPRG"));
        bean.setFIN_FUENTE_PRG(rs.getString("TX_FUENTEPROPPRG"));
        bean.setFIN_SUPUESTO_PRG(rs.getString("TX_SUPUESTOPROPPRG"));
    }

      public void  setDetailMarLogComponente(ProgEstrMarLogVO bean, ResultSet rs) throws SQLException{
        bean.setCOMP_COD_PRG(rs.getInt("NUM_COMPPRG_PK"));
        bean.setCOMP_DESC_PRG(rs.getString("TX_DESCCOMPPRG"));
        bean.setCOMP_FUENTE_PRG(rs.getString("TX_FUENTECOMPPRG"));
        bean.setCOMP_SUPUESTO_PRG(rs.getString("TX_SUPUESTOCOMPPRG"));
    }



 
     public List getlistMarLogFin(int codPrograma) throws SQLException {

        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<ProgEstrMarLogVO> lista = new ArrayList<ProgEstrMarLogVO>(0);
        //List lista=null;
        //lista=null;
        String sql = "";
        try {
           conn = DriverManager.getConnection(url, usuario, password);
           stmt = conn.createStatement();
           sql =" select   "+
                  "   f.*      "+
                  " from me_finprg f  "+
                  " where   f.num_codprg_pk="+codPrograma;
            
            rs = stmt.executeQuery(sql);
            ProgEstrMarLogVO bean = null;
            while (rs.next()) {
                bean = new ProgEstrMarLogVO();
               setDetailMarLogFin(bean, rs);
               lista.add(bean);
            }

        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return lista;
    }



     public List getlistFinInv(int codPrograma) throws SQLException {

        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<ProgEstrMarLogVO> lista = new ArrayList<ProgEstrMarLogVO>(0);
        //List lista=null;
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            stmt = conn.createStatement();
           sql =" select   "+
                  "   f.*      "+
                  " from ME_FINPYINV f  "+
                  " where   f.NUM_PYINV_PK="+codPrograma;

            rs = stmt.executeQuery(sql);
            ProgEstrMarLogVO bean = null;
            while (rs.next()) {
                bean = new ProgEstrMarLogVO();
               setFinInv(bean, rs);
               lista.add(bean);
            }

        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return lista;
    }


     public int getMuestrafin(int id) throws SQLException {
        PreparedStatement pstmt = null;
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<ProgEstrMarLogVO> lista = new ArrayList<ProgEstrMarLogVO>(0);
        int numero=0;
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            sql = " select   "+
                  "   f.*      "+
                  " from ME_FINPYINV f  "+
                  " where   f.NUM_PYINV_PK=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            rs = pstmt.executeQuery();
            ProgEstrMarLogVO bean = null;
            while (rs.next()) {
                bean = new ProgEstrMarLogVO();
                setMuestraF(bean, rs);
                lista.add(bean);
                numero=1;
            }
            rs.close();
            pstmt.close();
        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return numero;
    }



     public List getlistMarLogComponente(int codPrograma) throws SQLException {

        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<ProgEstrMarLogVO> lista = new ArrayList<ProgEstrMarLogVO>(0);
        //List lista=null;
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            stmt = conn.createStatement();
            sql =" select   "+
                  "   c.* ,mc.*       "+
                  " from me_compprg c, me_metacompprg mc  "+
                  " where  c.num_compprg_pk=mc.num_compprg_pk "+
                          " and  "+
                          " c.num_codprg_pk="+codPrograma;

            rs = stmt.executeQuery(sql);
            ProgEstrMarLogVO bean = null;
            while (rs.next()) {
                bean = new ProgEstrMarLogVO();
               setDetailMarLogComponente(bean, rs);
               lista.add(bean);
            }

        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return lista;
    }


     public List getlistMarLogProposito(int codPrograma) throws SQLException {

        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<ProgEstrMarLogVO> lista = new ArrayList<ProgEstrMarLogVO>(0);
        //List lista=null;
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            stmt = conn.createStatement();
            sql =" select   "+                      
                  "   p.* ,mp.*       "+                     
                  " from me_propprg p, me_metapropprg mp  "+
                  " where  p.num_propprg_pk=mp.num_propprg_pk "+
                          " and  "+
                          " p.num_codprg_pk="+codPrograma;


            rs = stmt.executeQuery(sql);
            ProgEstrMarLogVO bean = null;
            while (rs.next()) {
                bean = new ProgEstrMarLogVO();
               setDetailMarLogProposito(bean, rs);
               lista.add(bean);
            }

        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return lista;
    }

      public String spMan(ProgEstrMarLogVO bean) {
        String msj = "";
        //int codP=bean.getCODPRG();
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            CallableStatement cs = conn.prepareCall("{call Man_ProgEstrFin(?,?,?,?,?,?)}");
            cs.setString(1,bean.getOpcion());
            cs.setString(2, bean.getFIN_DESC_PRG());
            cs.setString(3, bean.getFIN_FUENTE_PRG());
            cs.setString(4, bean.getFIN_SUPUESTO_PRG());
            cs.setInt(5, bean.getCodUpdate());
            cs.setInt(   6, bean.getCODPRG());
            cs.execute();
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e.toString());
            msj = e.toString();
            return msj;
        }
        return msj;
    }


      public String spManFI(ProgEstrMarLogVO bean) {
        String msj = "";
        //int codP=bean.getCODPRG();
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            CallableStatement cs = conn.prepareCall("{call Man_ProgInvFin(?,?,?,?,?,?,?)}");
            cs.setString(1,bean.getOpcion());
            cs.setString(2, bean.getFIN_DESC_PRG());
            cs.setString(3, bean.getFIN_FUENTE_PRG());
            cs.setString(4, bean.getFIN_SUPUESTO_PRG());
            cs.setInt(5, bean.getCodUpdate());
            cs.setInt(   6, bean.getCODPRG());
            cs.setBigDecimal(   7, bean.getFIN_PRESUPPRG());
            cs.execute();
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e.toString());
            msj = e.toString();
            return msj;
        }
        return msj;
    }

}
